drop table if exists spmi_dm.dm_spmi_enter_port_weight_range_rate_month;
create table  spmi_dm.dm_spmi_enter_port_weight_range_rate_month(
    id                            varchar(255) comment 'ID'
    ,entry_mark_mth               date         comment '入账月份'
    ,piece_financial_center_id    varchar(255) comment '派件财务中心ID'
    ,receiver_province_id         varchar(255) comment '收件省份ID	'
    ,destination_id               varchar(255) comment '目的地ID'
    ,sum_type                     varchar(255) comment '汇总类型'
    ,piece_financial_center_code  varchar(255) comment '派件财务中心代码'
    ,piece_financial_center_name  varchar(255) comment '派件财务中心名称'
    ,receiver_province_name       varchar(255) comment '收件省份名称'
    ,destination_code             varchar(255) comment '目的地代码'
    ,destination_name             varchar(255) comment '目的地名称'
    ,all_weight_range_cnt         bigint            comment '数量合计'
    ,all_weight_range_rate        decimal(16, 6)    comment '占比合计'
    ,weight_range1_cnt            bigint comment '重量范围1数量, 0-0.5KG(含)件量'
    ,weight_range1_rate           decimal(16, 6)    comment '重量范围1占比, 0-0.5KG(含)件量'
    ,weight_range2_cnt            bigint            comment '重量范围2数量, 0.5KG-1KG(含)件量'
    ,weight_range2_rate           decimal(16, 6)    comment '重量范围2占比, 0.5KG-1KG(含)件量'
    ,weight_range3_cnt            bigint            comment '重量范围3数量, 1KG-3.2KG(含)件量'
    ,weight_range3_rate           decimal(16, 6)    comment '重量范围3占比, 1KG-3.2KG(含)件量'
    ,weight_range4_cnt            bigint            comment '重量范围4数量, 3.2KG以上件量'
    ,weight_range4_rate           decimal(16, 6)    comment '重量范围4占比, 3.2KG以上件量'
)ENGINE=OLAP
DUPLICATE KEY(`id` , `entry_mark_mth`, `piece_financial_center_id`, `receiver_province_id`, `destination_id`, `sum_type`)
COMMENT "派件费-进港件量月汇总"
PARTITION BY RANGE(`entry_mark_mth`)
(
    PARTITION  p202303 VALUES [('2023-03-01'), ('2023-04-01'))
    ,PARTITION p202304 VALUES [('2023-04-01'), ('2023-05-01'))
    ,PARTITION p202305 VALUES [('2023-05-01'), ('2023-06-01'))
    ,PARTITION p202306 VALUES [('2023-06-01'), ('2023-07-01'))
    ,PARTITION p202307 VALUES [('2023-07-01'), ('2023-08-01'))
)
DISTRIBUTED BY HASH(`receiver_province_id`, `destination_id`) BUCKETS 3
PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.time_zone" = "Asia/Shanghai",
    "dynamic_partition.start" = "-12",
    "dynamic_partition.end" = "2",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "30",
    "dynamic_partition.start_day_of_month" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);
